using System;
using System.Collections.Generic;
using System.Text;

namespace UDS.Management.Data.Job.Generator
{
    internal class SQLJobGenerator
    {
        private static string ErrorFormatString = "IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback";

        private static string ErrorFormatDeleteFooter = "\r\nCOMMIT TRANSACTION\r\nGOTO EndSave\r\nQuitWithRollback:\r\nIF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION\r\nEndSave:\r\n";

        private static string DiableOldJobFormatString = "SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'{0}')\r\n"
            + "\r\nIF (@JobID IS NOT NULL)"
            + "\r\nBEGIN"
            + "\r\nEXECUTE msdb.dbo.sp_update_job @job_id = @JobID , @enabled = 0"
            + "\r\nEND\r\n";

        private static string JobFormatString = "BEGIN TRANSACTION"
            + "\r\nDECLARE @JobID BINARY(16)"
            + "\r\nDECLARE @ReturnCode INT"
            + "\r\nSELECT @ReturnCode = 0"
            + "\r\nIF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'{0}') < 1"
            + "\r\nEXECUTE msdb.dbo.sp_add_category @name = N'{0}'\r\n"
            + "{1}\r\n"
            + "\r\nDECLARE @DBName varchar(100)"
            + "\r\nSET @DBName = DB_NAME()"
            + "\r\nSELECT @JobID = NULL"
            + "\r\nSELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'{2}')"
            + "\r\nIF (@JobID IS NOT NULL)"
            + "\r\n\tBEGIN"
            + "\r\n\tIF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0)))"
            + "\r\n\t\tBEGIN"
            + "\r\n\t\tRAISERROR (N'Unable to import job ''{2}'' since there is already a multi-server job with this name.', 16, 1)"
            + "\r\n\t\tGOTO QuitWithRollback"
            + "\r\n\t\tEND"
            + "\r\n\tELSE"
            + "\r\n\t\tEXECUTE msdb.dbo.sp_delete_job @job_name = N'{2}'\r\n\r\n"
            + "\r\n\tSELECT @JobID = NULL"
            + "\r\nEND\r\n";

         private static string JobFormatStringCore = "\r\nBEGIN"
            + "\r\n\tEXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'{1}', @owner_login_name = N'{2}', @description = N'{3}', @category_name = N'{0}', @enabled = {4}, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0"
            + "\r\n\t{7}\r\n"
            + "\r\n{5}\r\n"
            + "\r\n{6}\r\n"
            + "\r\n\tEXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'"
            + "\r\n\t{7}"
            + "\r\nEND"
            + "\r\nCOMMIT TRANSACTION"
            + "\r\nGOTO EndSave"
            + "\r\nQuitWithRollback:"
            + "\r\nIF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION"
            + "\r\nEndSave:\r\n";

        private static string JobStepFormatString = "\r\n\tEXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = {10}, @step_name = N'{0}', @command = N'{1}', @database_name = @DBName, @server = N'', @database_user_name = N'', @subsystem = N'{2}', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = {3}, @retry_interval = {4}, @output_file_name = N'', @on_success_step_id = {5}, @on_success_action = {6}, @on_fail_step_id = {7}, @on_fail_action = {8}\r\n\t{9}";

        private static string JobStartStepFormatString = "\r\n\tEXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = {0} \r\n\t{1}";

        private static string JobScheduleFormatString = "\r\n\tEXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'{0}', @enabled = {1}, @freq_type = {2}, @active_start_date = 20101001, @active_start_time = {3}, @freq_interval = {4}, @freq_subday_type = {5}, @freq_subday_interval = {6}, @freq_relative_interval = {7}, @freq_recurrence_factor = {8}, @active_end_date = 99991231, @active_end_time = {9}\r\n\t{10}";

        private SQLJobDefinition job;
        public SQLJobGenerator(SQLJobDefinition sqlJobDefinition)
        {
            job = sqlJobDefinition;
        }
        public string GenerateSQL()
        {
            if (job.Operation == OperationType.Create)
            {
                return string.Format(JobFormatString, job.Category, GetTheDisabledSQL(job.OldJobName), job.JobName + ConvertJobVersion(job.JobVersion) )
                 + string.Format(JobFormatStringCore, job.Category, job.JobName + ConvertJobVersion(job.JobVersion) , job.Owner, job.Description, (job.Enabled ? 1 : 0), GetTheJobStepSQL(), GetTheJobScheduleSQL(), ErrorFormatString);
            }
            else
            {
                if (job.Operation == OperationType.Delete)
                {
                    return string.Format(JobFormatString, job.Category, GetTheDisabledSQL(job.OldJobName), job.JobName + ConvertJobVersion(job.JobVersion)) + ErrorFormatDeleteFooter;
                }
                else
                {
                    return string.Empty; //need to build this.
                }
            }
        }

        private string GetTheJobStepSQL()
        {
            string retValTotal = string.Empty;
            bool isFirst = true;
            if (job.JobSteps != null)
            {
                SortedList<int, JobStepDefinition> sortedSteps = new SortedList<int, JobStepDefinition>();
                foreach (JobStepDefinition step in job.JobSteps)
                {
                    sortedSteps.Add(step.StepId, step);
                }
                foreach(int stepid in sortedSteps.Keys)
                {
                    JobStepDefinition step = sortedSteps[stepid];

                    retValTotal += string.Format(JobStepFormatString, step.StepName, step.StepCommand,
                        ConvertSubSystem(step.AgentSubSystem), step.RetryAttempts,
                        step.RetryAttemptsInterval, step.OnSuccessStepId, ConvertStepCompletion(step.OnSuccessAction),
                        step.OnFailStepId, ConvertStepCompletion(step.OnFailAction), ErrorFormatString, step.StepId);
                    if (isFirst)
                    {
                        isFirst = false;
                        retValTotal += string.Format(JobStartStepFormatString, job.StartStepID, ErrorFormatString);
                    }
                }
            }
            return retValTotal;
        }
        private string GetTheDisabledSQL(string oldJobName)
        {
            if (!string.IsNullOrEmpty(oldJobName))
            {
                return string.Format(DiableOldJobFormatString, oldJobName);
            }
            else
            {
                return string.Empty;
            }
        }
        private string GetTheJobScheduleSQL()
        {
            string retValTotal = string.Empty;
            if (job.JobSchedules != null)
            {
                foreach (JobScheduleDefinition sched in job.JobSchedules)
                {
                    retValTotal += string.Format(JobScheduleFormatString, sched.ScheduleName, (sched.Enabled ? 1 : 0),
                        ConvertFrequencyType(sched.FrequencyType), sched.ActiveStart, sched.FrequencyInterval,
                        ConvertFrequencySubDay(sched.FrequencySubDayType), sched.FrequencySubDayInterval, sched.FrequencyRelativeInterval,
                        sched.FrequencyRecurrenceFactor, sched.ActiveEnd, ErrorFormatString);
                }
            }
            return retValTotal;
        }

        #region Helper Conversion Functions
        private string ConvertJobVersion(string jobVersion)
        {
            if (string.IsNullOrEmpty(jobVersion))
            {
                return string.Empty;
            }
            else
            {
                return "_V_" + jobVersion.Replace(".", "_");
            }
        }
        private string ConvertSubSystem(AgentSubSystemEnumeration sys)
        {
            switch (sys)
            {
                case AgentSubSystemEnumeration.TransactSql:
                    return "TSQL";
                case AgentSubSystemEnumeration.ActiveScripting:
                    return "ActiveScripting";
                case AgentSubSystemEnumeration.CmdExec:
                    return "CmdExec";
                case AgentSubSystemEnumeration.Snapshot:
                    return "Snapshot";
                case AgentSubSystemEnumeration.LogReader:
                    return "LogReader";
                case AgentSubSystemEnumeration.Distribution:
                    return "Distribution";
                case AgentSubSystemEnumeration.Merge:
                    return "Merge";
                case AgentSubSystemEnumeration.QueueReader:
                    return "QueueReader";
                case AgentSubSystemEnumeration.AnalysisQuery:
                    return "ANALYSISQUERY";
                case AgentSubSystemEnumeration.AnalysisCommand:
                    return "ANALYSISCOMMAND";
                case AgentSubSystemEnumeration.Ssis:
                    return "Ssis";
                default:
                    return "TSQL";
            }
        }

        private int ConvertStepCompletion(StepCompletionActionEnumeration st)
        {
            switch (st)
            {
                case StepCompletionActionEnumeration.QuitWithSuccess:
                    return 1;
                case StepCompletionActionEnumeration.QuitWithFailure:
                    return 2;
                case StepCompletionActionEnumeration.GoToNextStep:
                    return 3;
                case StepCompletionActionEnumeration.GoToStep:
                    return 4;
                default:
                    return 1;
            }
        }

        private int ConvertFrequencyType(FrequencyTypesEnumeration ft)
        {
            switch (ft)
            {
                case FrequencyTypesEnumeration.Unknown:
                    return 0;
                case FrequencyTypesEnumeration.OneTime:
                    return 1;
                case FrequencyTypesEnumeration.Daily:
                    return 4;
                case FrequencyTypesEnumeration.Weekly:
                    return 8;
                case FrequencyTypesEnumeration.Monthly:
                    return 16;
                case FrequencyTypesEnumeration.MonthlyRelative:
                    return 32;
                case FrequencyTypesEnumeration.AutoStart:
                    return 64;
                case FrequencyTypesEnumeration.OnIdle:
                    return 128;
                default:
                    return 4;
            }
        }

        private int ConvertFrequencySubDay(FrequencySubDayTypesEnumeration fs)
        {
            switch (fs)
            {
                case FrequencySubDayTypesEnumeration.Unknown:
                    return 0;
                case FrequencySubDayTypesEnumeration.Once:
                    return 1;
                case FrequencySubDayTypesEnumeration.Second:
                    return 2;
                case FrequencySubDayTypesEnumeration.Minute:
                    return 4;
                case FrequencySubDayTypesEnumeration.Hour:
                    return 8;
                default:
                    return 8;
            }
        }
        #endregion
    }
}
